package edu.chen.dao.impl;

import edu.chen.bean.Product;
import edu.chen.dao.ProductDao;
import org.junit.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.*;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@Repository("productDaoImplByJdbc")
@Transactional
public class ProductDaoImplByJdbc implements ProductDao{
    private JdbcTemplate jdbcTemplate;

    /**
     * 因为JdbcTemplate需要自动装配,而无需提取,所以只有set
     * @param jdbcTemplate
     */
    @Autowired
    public void setJdbcTemplate(JdbcTemplate jdbcTemplate){
        this.jdbcTemplate = jdbcTemplate;
    }

    public int insert(final Product product) {
        final String sql = "INSERT  INTO product_ VALUES (DEFAULT ,?,?)";
        final KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement preparedStatement = connection.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
                PreparedStatementSetter preparedStatementSetter = new ArgumentPreparedStatementSetter(new Object[]{product.getName(),product.getPrice()});
                try {
                    if (preparedStatementSetter != null) {
                        preparedStatementSetter.setValues(preparedStatement);
                    }
                } finally {
                    if (preparedStatementSetter instanceof ParameterDisposer) {
                        ((ParameterDisposer) preparedStatementSetter).cleanupParameters();
                    }
                }
                return preparedStatement;
            }
        },keyHolder);
        return keyHolder.getKey().intValue();
    }


    public int delete(int id) {
        String sql = "DELETE  FROM product_ WHERE product_.id = ?";
        return jdbcTemplate.update(sql,id);
    }

    public int delete(String name) {
        String sql = "DELETE  FROM product_ WHERE product_.name = ?";
        return jdbcTemplate.update(sql,name);
    }

    public Product query(int id) {
        final Product product = new Product();
        String sql = "SELECT * FROM product_ WHERE id = ?";
        jdbcTemplate.query(sql,new Object[]{id}, new RowCallbackHandler() {
            public void processRow(ResultSet resultSet) throws SQLException {
                product.setId(resultSet.getInt("id"));
                product.setName(resultSet.getString("name"));
                product.setPrice(resultSet.getDouble("price"));
            }
        });
        return product;
    }

    public List<Product> query(String name) {
        final List<Product> products = new ArrayList<Product>();
        String sql = "SELECT * FROM product_ WHERE name = ?";
        jdbcTemplate.query(sql, new Object[]{name}, new RowCallbackHandler() {
            public void processRow(ResultSet resultSet) throws SQLException {
                do {
                    Product product = new Product();
                    product.setId(resultSet.getInt("id"));
                    product.setName(resultSet.getString("name"));
                    product.setPrice(resultSet.getDouble("price"));
                    products.add(product);
                }while (resultSet.next());
            }
        });
        return products;
    }
}
